package com.order.model;

import java.util.*;
import java.sql.Date;
import java.sql.*;
import java.io.*;

import javax.sql.*;
import javax.naming.*;

import com.orderdetail.model.OrderDetailService;
import com.orderdetail.model.Order_detailVO;

public class OrdDAO implements OrdDAO_interface {

	private static DataSource ds = null;
	static {
		try {
			Context ctx = new InitialContext();
			ds = (DataSource) ctx.lookup("java:comp/env/jdbc/ProjectDB");
		} catch (NamingException e) {
			e.printStackTrace();
		}
	}

	private final static String INSERT_ORDER_AND_DETAILS = "INSERT INTO ORD (ORD_ID ,MACCOUNT ,BUILD_TIME ,RECEIVER_NAME ,RECEIVER_ADD ,RECEIVER_TEL ,RECEIVER_EMAIL ,SEND_METHOD ,SEND_TIME ,TOTAL ,STATUS) VALUES (ORDER_ID_SEQ.NEXTVAL ,? ,? ,? ,? ,? ,? ,? ,? ,? ,?)";
	private final static String UPDATE_STATUS_STMT = "UPDATE ORD SET STATUS = ? WHERE ORD_ID = ?";
	private final static String GET_ALL_STMT = "SELECT ORD_ID ,MACCOUNT ,TO_CHAR(BUILD_TIME ,'yyyy-mm-dd') BUILD_TIME ,RECEIVER_NAME ,RECEIVER_ADD ,RECEIVER_TEL ,RECEIVER_EMAIL ,SEND_METHOD ,SEND_TIME ,TOTAL ,STATUS FROM ORD ORDER BY ORD_ID DESC";
	private final static String GET_ALL_BYSTATUS_STMT = "SELECT ORD_ID ,MACCOUNT ,TO_CHAR(BUILD_TIME ,'yyyy-mm-dd') BUILD_TIME ,RECEIVER_NAME ,RECEIVER_ADD ,RECEIVER_TEL ,RECEIVER_EMAIL ,SEND_METHOD ,SEND_TIME ,TOTAL ,STATUS FROM ORD WHERE STATUS > ? ORDER BY ORD_ID DESC";
	private final static String GET_OrdDetail_ByOrdId_STMT = "SELECT ORD_ID ,GOODSNO ,GOOD_TYPE ,GOOD_PIC ,ORDER_QT ,PRICE FROM ORDER_DETAIL WHERE ORD_ID = ?";
	private final static String GET_Ord_ByBuildTime_STMT = "SELECT ORD_ID ,MACCOUNT ,TO_CHAR(BUILD_TIME ,'yyyy-mm-dd') BUILD_TIME ,RECEIVER_NAME ,RECEIVER_ADD ,RECEIVER_TEL ,RECEIVER_EMAIL ,SEND_METHOD ,SEND_TIME ,TOTAL ,STATUS FROM ORD WHERE BUILD_TIME BETWEEN ? AND ?";
	private final static String GET_ONE_STMT = "SELECT ORD_ID ,MACCOUNT ,TO_CHAR(BUILD_TIME ,'yyyy-mm-dd') BUILD_TIME ,RECEIVER_NAME ,RECEIVER_ADD ,RECEIVER_TEL ,RECEIVER_EMAIL ,SEND_METHOD ,SEND_TIME ,TOTAL ,STATUS FROM ORD WHERE ORD_ID = ?";
	
	@Override
	public Long insert(OrdVO ordVO ,List<Order_detailVO> ordetails) {
		Connection con = null;
		PreparedStatement pst = null;
		String nextOrdId = null;
		
		try {
			con = ds.getConnection();
			
			con.setAutoCommit(false);
			
			String[] col = {"ORD_ID"};
			pst = con.prepareStatement(INSERT_ORDER_AND_DETAILS ,col);
			pst.setString(1, ordVO.getMaccount());
			pst.setDate(2, ordVO.getBuild_time());
			pst.setString(3, ordVO.getReceiver_name());
			pst.setString(4, ordVO.getReceiver_add());
			pst.setString(5, ordVO.getReceiver_tel());
			pst.setString(6, ordVO.getReceiver_email());
			pst.setInt(7, ordVO.getSend_method());
			pst.setDate(8, ordVO.getSend_time());
			pst.setInt(9, ordVO.getTotal());
			pst.setInt(10, ordVO.getStatus());
			pst.executeUpdate();

			ResultSet rs = pst.getGeneratedKeys();
			
			rs.next();
			nextOrdId = rs.getString(1);
			rs.close();
			
			OrderDetailService ods = new OrderDetailService();
			
			for(Order_detailVO odVO : ordetails) {
				odVO.setOrd_id(new Long(nextOrdId));				
				ods.addOrderDetail(odVO.getOrd_id(), odVO.getGoodsno(), odVO.getGood_type(), odVO.getGood_pic(), odVO.getOrder_qt(), odVO.getPrice() ,con);
			}
			
			con.commit();
			con.setAutoCommit(true);
			
		} catch (SQLException e) {
			e.printStackTrace();
			try {
				con.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		} finally {
			if (pst != null) {
				try {
					pst.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}

			if (con != null) {
				try {
					con.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
		return Long.parseLong(nextOrdId);

	}

	@Override
	public void updateStatus(OrdVO ordVO) {
		Connection con = null;
		PreparedStatement pst = null;

		try {
			con = ds.getConnection();
			pst = con.prepareStatement(UPDATE_STATUS_STMT);
			pst.setInt(1, ordVO.getStatus());
			pst.setLong(2 ,ordVO.getOrd_id());
			
			pst.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (pst != null) {
				try {
					pst.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}

			if (con != null) {
				try {
					con.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
	}

	@Override
	public List<OrdVO> findByBuildDate(Date buildTime, Date buildTime2) {
		ArrayList<OrdVO> list = new ArrayList<OrdVO>();
		OrdVO ordVO = null;

		Connection con = null;
		PreparedStatement pst = null;
		ResultSet rs = null;

		try {
			con = ds.getConnection();
			pst = con.prepareStatement(GET_Ord_ByBuildTime_STMT);
			pst.setDate(1, buildTime);
			pst.setDate(2, buildTime2);
			rs = pst.executeQuery();

			while (rs.next()) {
				ordVO = new OrdVO();
				ordVO.setOrd_id(rs.getLong("ord_id"));
				ordVO.setMaccount(rs.getString("maccount"));
				ordVO.setBuild_time(rs.getDate("build_time"));
				ordVO.setReceiver_name(rs.getString("receiver_name"));
				ordVO.setReceiver_add(rs.getString("receiver_add"));
				ordVO.setReceiver_tel(rs.getString("receiver_tel"));
				ordVO.setReceiver_email(rs.getString("receiver_email"));
				ordVO.setSend_method(rs.getInt("send_method"));
				ordVO.setSend_time(rs.getDate("send_time"));
				ordVO.setTotal(rs.getInt("total"));
				ordVO.setStatus(rs.getInt("status"));
				list.add(ordVO);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (rs != null)
				try {
					rs.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			if (pst != null)
				try {
					pst.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			if (con != null)
				try {
					con.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}

		return list;
	}

	@Override
	public List<OrdVO> getAll() {
		ArrayList<OrdVO> list = new ArrayList<OrdVO>();
		OrdVO ordVO = null;

		Connection con = null;
		PreparedStatement pst = null;
		ResultSet rs = null;

		try {
			con = ds.getConnection();
			pst = con.prepareStatement(GET_ALL_STMT);
			rs = pst.executeQuery();

			while (rs.next()) {
				ordVO = new OrdVO();
				ordVO.setOrd_id(rs.getLong("ord_id"));
				ordVO.setMaccount(rs.getString("maccount"));
				ordVO.setBuild_time(rs.getDate("build_time"));
				ordVO.setReceiver_name(rs.getString("receiver_name"));
				ordVO.setReceiver_add(rs.getString("receiver_add"));
				ordVO.setReceiver_tel(rs.getString("receiver_tel"));
				ordVO.setReceiver_email(rs.getString("receiver_email"));
				ordVO.setSend_method(rs.getInt("send_method"));
				ordVO.setSend_time(rs.getDate("send_time"));
				ordVO.setTotal(rs.getInt("total"));
				ordVO.setStatus(rs.getInt("status"));
				list.add(ordVO);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (rs != null)
				try {
					rs.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			if (pst != null)
				try {
					pst.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			if (con != null)
				try {
					con.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}

		return list;
	}

	@Override
	public Set<Order_detailVO> getOrderDetailByOrdId(Long ordId) {
		Set<Order_detailVO> list = new LinkedHashSet<Order_detailVO>();
		Order_detailVO odVO = null;

		Connection con = null;
		PreparedStatement pst = null;
		ResultSet rs = null;

		try {
			con = ds.getConnection();
			pst = con.prepareStatement(GET_OrdDetail_ByOrdId_STMT);
			pst.setLong(1, ordId);
			rs = pst.executeQuery();
			
			while (rs.next()) {
				odVO = new Order_detailVO();
				odVO.setOrd_id(rs.getLong("ord_id"));
				odVO.setGoodsno(rs.getInt("goodsno"));
				odVO.setGood_type(rs.getInt("good_type"));

//				ByteArrayOutputStream bos = new ByteArrayOutputStream();
//				byte[] i = new byte[1024];
//				int n = 0;
//				while ((n = rs.getBinaryStream("good_pic").read(i)) != -1) {
//					bos.write(i, n, i.length);
//				}

//				odVO.setGood_pic(bos.toByteArray());
				odVO.setGood_pic(null);
				odVO.setOrder_qt(rs.getInt("order_qt"));
				odVO.setPrice(rs.getInt("price"));
				list.add(odVO);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 
//		catch (IOException e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		}
			finally {
			if (rs != null)
				try {
					rs.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			if (pst != null)
				try {
					pst.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			if (con != null)
				try {
					con.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}
		return list;
	}

	@Override
	public OrdVO findByPrimaryKey(Long ordId) {
		OrdVO ordVO = null;

		Connection con = null;
		PreparedStatement pst = null;
		ResultSet rs = null;

		try {
			con = ds.getConnection();
			pst = con.prepareStatement(GET_ONE_STMT);
			pst.setLong(1, ordId);
			rs = pst.executeQuery();
			
			while (rs.next()) {
				ordVO = new OrdVO();
				ordVO.setOrd_id(rs.getLong("ord_id"));
				ordVO.setMaccount(rs.getString("maccount"));
				ordVO.setBuild_time(rs.getDate("build_time"));
				ordVO.setReceiver_name(rs.getString("receiver_name"));
				ordVO.setReceiver_add(rs.getString("receiver_add"));
				ordVO.setReceiver_tel(rs.getString("receiv" +
						"er_tel"));
				ordVO.setReceiver_email(rs.getString("receiver_email"));
				ordVO.setSend_method(rs.getInt("send_method"));
				ordVO.setSend_time(rs.getDate("send_time"));
				ordVO.setTotal(rs.getInt("total"));
				ordVO.setStatus(rs.getInt("status"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (rs != null)
				try {
					rs.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			if (pst != null)
				try {
					pst.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			if (con != null)
				try {
					con.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}	
		return ordVO;
	}

	@Override
	public List<OrdVO> getAllByStatus(Integer status) {
		ArrayList<OrdVO> list = new ArrayList<OrdVO>();
		OrdVO ordVO = null;

		Connection con = null;
		PreparedStatement pst = null;
		ResultSet rs = null;

		try {
			con = ds.getConnection();
			pst = con.prepareStatement(GET_ALL_BYSTATUS_STMT);
			pst.setInt(1, status);
			rs = pst.executeQuery();

			while (rs.next()) {
				ordVO = new OrdVO();
				ordVO.setOrd_id(rs.getLong("ord_id"));
				ordVO.setMaccount(rs.getString("maccount"));
				ordVO.setBuild_time(rs.getDate("build_time"));
				ordVO.setReceiver_name(rs.getString("receiver_name"));
				ordVO.setReceiver_add(rs.getString("receiver_add"));
				ordVO.setReceiver_tel(rs.getString("receiver_tel"));
				ordVO.setReceiver_email(rs.getString("receiver_email"));
				ordVO.setSend_method(rs.getInt("send_method"));
				ordVO.setSend_time(rs.getDate("send_time"));
				ordVO.setTotal(rs.getInt("total"));
				ordVO.setStatus(rs.getInt("status"));
				list.add(ordVO);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (rs != null)
				try {
					rs.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			if (pst != null)
				try {
					pst.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			if (con != null)
				try {
					con.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}

		return list;
	}

}
